package dao.impl;

import alumno.sql.ConectaDb;
import dao.DaoAlumnos;
import dto.Alumnos;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

public class DaoAlumnosImpl implements DaoAlumnos {

    private final ConectaDb db;

    public DaoAlumnosImpl() {
        this.db = new ConectaDb();
    }

    @Override
    public List<Alumnos> alumnosQry() {

        List<Alumnos> list = null;
        String sql = "SELECT "
                + "idalumno,"
                + "alumno,"
                + "nota1,"
                + "nota2,"
                + "nota3,"
                + "comentario "
                + "FROM notas";

        Connection cn = db.getConnection();
        if (cn != null) {
            try {
                PreparedStatement ps = cn.prepareStatement(sql);
                ResultSet rs = ps.executeQuery();

                list = new LinkedList<Alumnos>();
                while (rs.next()) {
                    Alumnos a = new Alumnos();

                    a.setIdalumno(rs.getInt(1));
                    a.setAlumno(rs.getString(2));
                    a.setNota1(rs.getInt(3));
                    a.setNota2(rs.getInt(4));
                    a.setNota3(rs.getInt(5));
                    a.setComentario(rs.getString(6));

                    list.add(a);
                }
            } catch (SQLException e) {
            } finally {
                try {
                    cn.close();
                } catch (SQLException e) {
                }
            }
        }
        return list;
    }

    @Override
    public String alumnosIns(Alumnos alumnos) {
        String error = null;
        String sql = "INSERT notas("
                + "alumno,"
                + "nota1,"
                + "nota2,"
                + "nota3,"
                + "comentario"
                + ") VALUES(?,?,?,?,?)";

        Connection cn = db.getConnection();
        if (cn != null) {
            try {
                PreparedStatement ps = cn.prepareStatement(sql);
                ps.setString(1, alumnos.getAlumno());
                ps.setInt(2, alumnos.getNota1());
                ps.setInt(3, alumnos.getNota2());
                ps.setInt(4, alumnos.getNota3());
                ps.setString(5, alumnos.getComentario());

                int ctos = ps.executeUpdate();
                if (ctos == 0) {
                    error = "0 filas afectadas";
                }

            } catch (SQLException e) {
                error = e.getMessage();
            } finally {
                try {
                    cn.close();
                } catch (SQLException e) {
                    error = e.getMessage();
                }
            }
        }

        return error;
    }

    @Override
    public String alumnosDel(List<Integer> ids) {
        String error = null;
        String sql = "DELETE FROM notas WHERE idalumno=?";

        Connection cn = db.getConnection();
        if (cn != null) {
            boolean ok = false;
            try {
                cn.setAutoCommit(false);

                PreparedStatement ps = cn.prepareStatement(sql);
                for (Integer x : ids) {
                    ps.setInt(1, x);
                    int ctos = ps.executeUpdate();
                    if (ctos == 0) {
                        throw new SQLException("ID(s) no registrado");
                    }
                }
                ok = true;

            } catch (SQLException e) {
                error = e.getMessage();
            } finally {
                try {
                    if (ok) {
                        cn.commit();
                    } else {
                        cn.rollback();
                    }

                    cn.setAutoCommit(true);
                    cn.close();

                } catch (SQLException e) {
                    error = e.getMessage();
                }
            }
        }

        return error;
    }

    @Override
    public Alumnos alumnosGet(Integer idalumnos) {
        Alumnos alumnos = null;
        String sql = "SELECT "
                + "idalumno,"
                + "alumno,"
                + "nota1,"
                + "nota2,"
                + "nota3,"
                + "comentario "
                + "FROM notas "
                + "WHERE idalumno=?";

        Connection cn = db.getConnection();
        if (cn != null) {
            try {
                PreparedStatement ps = cn.prepareStatement(sql);
                ps.setInt(1, idalumnos);

                ResultSet rs = ps.executeQuery();
                if (rs.next()) {
                    alumnos = new Alumnos();

                    alumnos.setIdalumno(rs.getInt(1));
                    alumnos.setAlumno(rs.getString(2));
                    alumnos.setNota1(rs.getInt(3));
                    alumnos.setNota2(rs.getInt(4));
                    alumnos.setNota3(rs.getInt(5));
                    alumnos.setComentario(rs.getString(6));
                }
            } catch (SQLException e) {
            } finally {
                try {
                    cn.close();
                } catch (SQLException e) {
                }
            }
        }

        return alumnos;

    }

    @Override
    public String alumnosUpd(Alumnos alumnos) {
        String error = null;
        String sql = "UPDATE notas SET "
                + "alumno=?,"
                + "nota1=?,"
                + "nota2=?,"
                + "nota3=?,"
                + "comentario=? "
                + "WHERE idalumno=?";

        Connection cn = db.getConnection();
        if (cn != null) {
            try {
                PreparedStatement ps = cn.prepareStatement(sql);
                ps.setString(1,alumnos.getAlumno());
                ps.setInt(2, alumnos.getNota1());
                ps.setInt(3, alumnos.getNota2());
                ps.setInt(4, alumnos.getNota3());
                ps.setString(5, alumnos.getComentario());
                ps.setInt(6, alumnos.getIdalumno());
                
                int ctos = ps.executeUpdate();
                if (ctos == 0) {
                    error = "0 filas afectadas";
                }

            } catch (SQLException e) {
                error = e.getMessage();
            } finally {
                try {
                    cn.close();
                } catch (SQLException e) {
                    error = e.getMessage();
                }
            }
        }

        return error;
    }

}
